package net.ghy.statistics;

import java.io.File;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import net.ghy.statistics.utils.EUtils;

import dao.hb.BxMidle;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Alignment;
import jxl.write.Border;
import jxl.write.BorderLineStyle;
import jxl.write.Colour;
import jxl.write.Label;
import jxl.write.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

@SuppressWarnings("deprecation")
public class ExcelStyle {

	jxl.write.WritableSheet ws;
	jxl.write.WritableWorkbook wwb;

	public ExcelStyle(File file, String title) {
		wwb = null;
		try {
			wwb = Workbook.createWorkbook(file);
			ws = wwb.createSheet(title, 0);
			for (int i = 0; i < 600; i++) {
				try {
					ws.setRowView(i, 360, false);
				} catch (RowsExceededException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public void setStyleToExcel(String type, String start, String end) {
		if (wwb != null) {
			try {
				// 设置默认字体
				jxl.write.WritableFont wfc = new jxl.write.WritableFont(
						WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
						UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
				// 设置title字体
				jxl.write.WritableFont titleFont = new WritableFont(
						WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false,
						UnderlineStyle.SINGLE, jxl.format.Colour.BLACK);
				// 设置时间字体
				jxl.write.WritableFont datewfc = new jxl.write.WritableFont(
						WritableFont.ARIAL, 10, WritableFont.BOLD, false,
						UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
				// 设置落款字体
				jxl.write.WritableFont endFont = new WritableFont(
						WritableFont.ARIAL, 10, WritableFont.BOLD);

				// ***************************Excel表格字体格式Start***********************//
				int charTitle = 11;// 标题字体大小
				int charNormal = 10;// 标题字体大小
				// 添加带有字型Formatting的对象

				// *******************************表头格式部分
				jxl.write.WritableFont titleHead = new jxl.write.WritableFont(
						WritableFont.createFont("宋体"), charTitle,
						WritableFont.NO_BOLD);

				jxl.write.WritableCellFormat titleHeadFormat = new jxl.write.WritableCellFormat(
						titleHead);
				titleHeadFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
				titleHeadFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
				titleHeadFormat.setAlignment(Alignment.CENTRE); // 水平对齐
				titleHeadFormat.setWrap(true); // 是否换行

				// *******************************表体数据内容格式部分

				jxl.write.WritableFont DataFontCotent = new jxl.write.WritableFont(
						WritableFont.createFont("ARIA"), 9);

				jxl.write.WritableCellFormat DataFontCotentFormat = new jxl.write.WritableCellFormat(
						DataFontCotent);
				DataFontCotentFormat
						.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
				DataFontCotentFormat
						.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
				DataFontCotentFormat.setAlignment(Alignment.LEFT); // 水平对齐
				DataFontCotentFormat.setWrap(true); // 是否换行

				// 用于带有formatting的Number对象
				WritableFont nf = new WritableFont(
						WritableFont.createFont("Times New Roman"), charNormal);
				jxl.write.NumberFormat nf1 = new jxl.write.NumberFormat("#.##");
				jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(
						nf1);
				wcfN.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
				wcfN.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
				wcfN.setAlignment(Alignment.CENTRE);// 水平对齐
				wcfN.setWrap(true); // 是否换行

				// ***************************Excel表格字体格式End***********************//

				// 设置左右上下边距
				ws.getSettings().setLeftMargin(0.6);
				ws.getSettings().setRightMargin(0.6);
				ws.getSettings().setTopMargin(0.3);
				ws.getSettings().setBottomMargin(0.3);
				// 设置行高
				WritableCellFormat detFormat = new WritableCellFormat(wfc);
				// 用于Number的格式

				WritableCellFormat firstFormat = new WritableCellFormat();
				WritableCellFormat dataFormat = new WritableCellFormat();
				WritableCellFormat titleFormat = new WritableCellFormat(
						titleFont);
				WritableCellFormat dateFormat = new WritableCellFormat(datewfc);// 时间格式
				WritableCellFormat endFormat = new WritableCellFormat(endFont);// 落款样式
				// WritableCellFormat backFormat = new WritableCellFormat(); //
				// 背景色

				// 标题行具体格式
				firstFormat.setAlignment(jxl.format.Alignment.CENTRE);
				firstFormat.setBackground(Colour.GRAY_25); // 背静色
				firstFormat.setBorder(jxl.format.Border.ALL,
						jxl.format.BorderLineStyle.DOUBLE,
						jxl.format.Colour.GRAY_50);
				// 内容行具体格式
				dataFormat.setBorder(jxl.format.Border.ALL,
						jxl.format.BorderLineStyle.DOUBLE,
						jxl.format.Colour.GRAY_50);
				dataFormat.setBackground(Colour.LIGHT_GREEN); // 背静色
				dataFormat.setAlignment(Alignment.CENTRE); // 设置对齐方式
				dataFormat.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置对齐方式
				try {
					if (type.equals("Q")) {
						setbt("Q", start+"~"+end+"医疗报销全院统计信息", titleHeadFormat, start, end);
					}
					if(type.equals("B")){
						setbt("B", start.replace("-", "/")+"~"+end.replace("-", "/")+"医疗报销全院统计信息", titleHeadFormat, start, end);
					}
					if (type.equals("A")) {
						setbt("A",start.replace("-", "/")+"~"+end.replace("-", "/")+"医疗报销年龄段统计信息", titleHeadFormat, start, end);
					}
					if (type.equals("J")) {
						setbt("J", start.replace("-", "/")+"~"+end.replace("-", "/")+"医疗报销计划生育统计信息", titleHeadFormat, start, end);
					}
					if (type.equals("Y")) {
						setbt("Y", start.replace("-", "/")+"~"+end.replace("-", "/")+"医疗报销大病药物统计信息", titleHeadFormat, start, end);
					}
					if (type.equals("DQ")) {
						setbt("DQ", start.replace("-", "/")+"~"+end.replace("-", "/")+"医疗报销大病器械统计信息", titleHeadFormat, start, end);
					}
					if (type.equals("U")) {
						setbt("U", start.replace("-", "/")+"~"+end.replace("-", "/")+"用户个人医疗报销统计信息", titleHeadFormat, start, end);
					}
					if (type.equals("AB")) {
						setbt("AB", start.replace("-", "/")+"~"+end.replace("-", "/")+"医疗报销年龄段统计信息", titleHeadFormat, start, end);
					}
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void setbt(String type, String label,
			WritableCellFormat titleHeadFormat, String start, String end) {
		// **** Excel表头部分 ****
		if(type.equals("B")){
			int startcolumn = 3;
			try {
				ws.addCell(new Label(0, 0, label));
				ws.mergeCells(0, 0, 13, 0);
				ws.setColumnView(0, 10, titleHeadFormat);
				ws.addCell(new Label(0, 1, "状态"));
				ws.mergeCells(0, 1, 0, 2);
				ws.setColumnView(0, 10, titleHeadFormat);// 设置宽度
				ws.addCell(new Label(0, 3, "在职"));
				ws.mergeCells(0, 3, 0, EUtils.getZzUser(start, end).size()
						+ EUtils.getDepList(start, end).size() + 3);
				ws.addCell(new Label(1, 1, "部门"));
				ws.mergeCells(1, 1, 1, 2);
				ws.setColumnView(1, 10, titleHeadFormat);// 设置宽度
				ws.addCell(new Label(2, 1, "姓名"));
				ws.mergeCells(2, 1, 2, 2);
				ws.setColumnView(2, 11, titleHeadFormat);// 设置宽度
				setGybt(titleHeadFormat, startcolumn);
				setGybtBao(titleHeadFormat, startcolumn);
			} catch (RowsExceededException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		  }else if (type.equals("Q")) {
			int startcolumn = 3;
			try {
				ws.addCell(new Label(0, 0, label));
				ws.mergeCells(0, 0, 10, 0);
				ws.setColumnView(0, 10, titleHeadFormat);
				ws.addCell(new Label(0, 1, "状态"));
				ws.mergeCells(0, 1, 0, 2);
				ws.setColumnView(0, 10, titleHeadFormat);// 设置宽度
				ws.addCell(new Label(0, 3, "在职"));
				ws.mergeCells(0, 3, 0, EUtils.getZzUser(start, end).size()
						+ EUtils.getDepList(start, end).size() + 3);
				ws.addCell(new Label(1, 1, "部门"));
				ws.mergeCells(1, 1, 1, 2);
				ws.setColumnView(1, 10, titleHeadFormat);// 设置宽度
				ws.addCell(new Label(2, 1, "姓名"));
				ws.mergeCells(2, 1, 2, 2);
				ws.setColumnView(2, 11, titleHeadFormat);// 设置宽度
				setGybt(titleHeadFormat, startcolumn);
			} catch (RowsExceededException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} else if (type.equals("A")) {
			int startcolumn = 2;
			try {
				ws.addCell(new Label(0, 0, label));
				ws.mergeCells(0, 0, 9, 0);
				ws.setColumnView(0, 10, titleHeadFormat);
				ws.addCell(new Label(0, 1, "状态"));
				ws.mergeCells(0, 1, 0, 2);
				ws.setColumnView(0, 10, titleHeadFormat);// 设置宽度
				// 修改
				ws.addCell(new Label(0, 3, "在职"));
				ws.mergeCells(0, 3, 0, 8);
				ws.addCell(new Label(1, 1, "年龄段"));
				ws.mergeCells(1, 1, 1, 2);
				ws.setColumnView(1, 10, titleHeadFormat);// 设置宽度
				setGybt(titleHeadFormat, startcolumn);
			} catch (RowsExceededException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} else if (type.equals("U")) {
			int startcolumn = 3;
			try {
				ws.addCell(new Label(0, 0, label));
				ws.mergeCells(0, 0, 6, 0);
				ws.setColumnView(0, 10, titleHeadFormat);// 设置宽度
				ws.addCell(new Label(0, 1, "状态"));
				ws.setColumnView(0, 15, titleHeadFormat);// 设置宽度
				ws.mergeCells(0, 1, 0, 2);
				ws.addCell(new Label(1, 1, "报销编号"));
				ws.setColumnView(1, 15, titleHeadFormat);// 设置宽度
				ws.mergeCells(1, 1, 1, 2);
				ws.addCell(new Label(2, 1, "看病日期"));
				ws.setColumnView(2, 20, titleHeadFormat);// 设置宽度
				ws.mergeCells(2, 1, 2, 2);
				ws.addCell(new Label(startcolumn, 2, "自付1"));
				ws.setColumnView(startcolumn, 20, titleHeadFormat);
				ws.addCell(new Label(startcolumn + 1, 2, "自付2"));
				ws.setColumnView(startcolumn + 1, 20, titleHeadFormat);
				ws.addCell(new Label(startcolumn + 2, 2, "自费"));
				ws.setColumnView(startcolumn + 2, 20, titleHeadFormat);
				ws.addCell(new Label(startcolumn + 3, 2, "合计"));
				ws.setColumnView(startcolumn + 3, 20, titleHeadFormat);
			} catch (RowsExceededException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else if(type.equals("AB")){
			int startcolumn = 2;
			try {
				ws.addCell(new Label(0, 0, label));
				ws.mergeCells(0, 0, 12, 0);
				ws.setColumnView(0, 10, titleHeadFormat);
				ws.addCell(new Label(0, 1, "状态"));
				ws.mergeCells(0, 1, 0, 2);
				ws.setColumnView(0, 10, titleHeadFormat);// 设置宽度
				// 修改
				ws.addCell(new Label(0, 3, "在职"));
				ws.mergeCells(0, 3, 0, 8);
				ws.addCell(new Label(1, 1, "年龄段"));
				ws.mergeCells(1, 1, 1, 2);
				ws.setColumnView(1, 10, titleHeadFormat);// 设置宽度
				setGybtAB(titleHeadFormat, startcolumn);
				
			 }catch (RowsExceededException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (WriteException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		} else {
			int startcolumn = 1;
			try {
				ws.addCell(new Label(0, 0, label));
				ws.mergeCells(0, 0, 4, 0);
				ws.addCell(new Label(0, 1, "姓名"));
				ws.setColumnView(0, 10, titleHeadFormat);// 设置宽度
				ws.mergeCells(0, 1, 0, 2);
				// ws.setColumnView(2, 11, titleHeadFormat);
				if (type.equals("J"))
					setTybt(titleHeadFormat, startcolumn, "计划生育", 20);
				else if (type.equals("Y")) {
					setTybt(titleHeadFormat, startcolumn, "大病药物", 20);
				} else if (type.equals("DQ")) {
					setTybt(titleHeadFormat, startcolumn, "大病器械", 20);
				} else
					setTybt(titleHeadFormat, startcolumn, "门诊", 20);
			} catch (RowsExceededException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

	}

	public void setGybt(WritableCellFormat titleHeadFormat, int startcolumn) {
		setTybt(titleHeadFormat, startcolumn, "门诊", 10);
		setTybt(titleHeadFormat, startcolumn + 4, "住院", 10);

	}
	public void setGybtBao(WritableCellFormat titleHeadFormat, int startcolumn) {
		setTybt(titleHeadFormat, startcolumn, "门诊", 10);
		setTybt(titleHeadFormat, startcolumn + 4, "住院", 10);
		setGybtBgf(titleHeadFormat, startcolumn + 8, "包干费", 20);

	}
	
	public void setGybtAB(WritableCellFormat titleHeadFormat, int startcolumn){
		setTybt(titleHeadFormat, startcolumn, "门诊", 10);
		setTybt(titleHeadFormat, startcolumn + 4, "住院", 10);
		setGybtBgf(titleHeadFormat, startcolumn + 8, "包干费", 20);
	}
	
	
	public void setGybtBgf(WritableCellFormat titleHeadFormat, int startcolumn,
			String title, int width) {
		try {
			ws.addCell(new Label(startcolumn, 1, title));
			ws.mergeCells(startcolumn, 1, startcolumn + 2, 1);
			ws.addCell(new Label(startcolumn, 2, "门诊住院合计"));
			ws.setColumnView(startcolumn, width, titleHeadFormat);
			ws.addCell(new Label(startcolumn + 1, 2, "包干费"));
			ws.setColumnView(startcolumn + 1, width, titleHeadFormat);
			ws.addCell(new Label(startcolumn + 2, 2, "实际报销总额"));
			ws.setColumnView(startcolumn + 2, width, titleHeadFormat);
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	

	public void setTybt(WritableCellFormat titleHeadFormat, int startcolumn,
			String title, int width) {
		try {
			ws.addCell(new Label(startcolumn, 1, title));
			ws.mergeCells(startcolumn, 1, startcolumn + 3, 1);
			ws.addCell(new Label(startcolumn, 2, "自付1"));
			ws.setColumnView(startcolumn, width, titleHeadFormat);
			ws.addCell(new Label(startcolumn + 1, 2, "自付2"));
			ws.setColumnView(startcolumn + 1, width, titleHeadFormat);
			ws.addCell(new Label(startcolumn + 2, 2, "自费"));
			ws.setColumnView(startcolumn + 2, width, titleHeadFormat);
			ws.addCell(new Label(startcolumn + 3, 2, "合计"));
			ws.setColumnView(startcolumn + 3, width, titleHeadFormat);
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	//

	// 内容部分 匹配部门及年龄统计
	public int addExcel(Map<String, List> map, int colstart, int rowstart)
			throws RowsExceededException, WriteException {
		// 姓名从2 3 开始()
		Iterator it = map.keySet().iterator();
		int rowsnum = 0;
		while (it.hasNext()) {
			String key = it.next().toString();
			if (!"合计".equals(key)) {
				List value = map.get(key);
				ws.addCell(new Label(colstart - 1, rowstart, key));
				addData(value, colstart, rowstart);
				// ws.setColumnView(i+4, 10, titleHeadFormat);
				rowstart++;
				rowsnum++;
			}

		}
		if (map.containsKey("合计")) {
			List value = map.get("合计");
			ws.addCell(new Label(colstart - 1, rowstart, "合计"));
			addData(value, colstart, rowstart);
			rowstart++;
			rowsnum++;
		}
		return rowsnum;
	}
	
	//	内容部分 匹配部门扣除包干费
	public int addExcelBao(Map<String, List> map, int colstart, int rowstart)
			throws RowsExceededException, WriteException {
		// 姓名从2 3 开始()
		Iterator it = map.keySet().iterator();
		int rowsnum = 0;
		while (it.hasNext()) {
			String key = it.next().toString();
			if (!"合计".equals(key)) {
				List value = map.get(key);
				ws.addCell(new Label(colstart - 1, rowstart, key));
				addData(value, colstart, rowstart);
				// ws.setColumnView(i+4, 10, titleHeadFormat);
				rowstart++;
				rowsnum++;
			}

		}
		if (map.containsKey("合计")) {
			List value = map.get("合计");
			ws.addCell(new Label(colstart - 1, rowstart, "合计"));
			addData(value, colstart, rowstart);
			rowstart++;
			rowsnum++;
		}
		return rowsnum;
	}

	public void addtyData(List<Map> data, int colstart, int rowstart)
			throws RowsExceededException, WriteException {
		for (int k = 0; k < data.size(); k++) {
			Map<String, List> bm = (Map<String, List>) data.get(k);
			Iterator itype = bm.keySet().iterator();
			while (itype.hasNext()) {
				String type = itype.next().toString();
				BxMidle deList = (BxMidle) bm.get(type);
				ws.addCell(new Label(colstart - 1, rowstart, type));
				ws.addCell(new Label(colstart, rowstart, deList.getPayone()
						.toString()));
				ws.addCell(new Label(colstart + 1, rowstart, deList.getPaytwo()
						.toString()));
				ws.addCell(new Label(colstart + 2, rowstart, deList.getPayall()
						.toString()));
				ws.addCell(new Label(colstart + 3, rowstart, deList.getBxSum()
						.toString()));
				rowstart++;
			}
		}
	}

	public int addUsertyData(Map<String, Map> data, int colstart, int rowstart)
			throws RowsExceededException, WriteException {
		Iterator wkstate = data.keySet().iterator();
		while (wkstate.hasNext()) {
			String state = wkstate.next().toString();
			Map<String, List> bm = (Map<String, List>) data.get(state);
			Iterator itype = bm.keySet().iterator();
			int begin = rowstart;
			ws.addCell(new Label(colstart - 2, rowstart, state));
			while (itype.hasNext()) {
				String type = itype.next().toString();
				BxMidle deList = (BxMidle) bm.get(type);
				if (!type.equals("实扣包干费")) {
					if (type.equals("合计")) {
						ws.addCell(new Label(colstart - 1, rowstart, type));
					}
					else{
						ws.addCell(new Label(colstart - 1, rowstart, deList.getDid().toString()));
					}
					if (deList.getDtLook() != null) {
							ws.addCell(new Label(colstart, rowstart, deList
									.getDtLook().toString()));
						}
						ws.addCell(new Label(colstart + 1, rowstart, deList
								.getPayone().toString()));
						ws.addCell(new Label(colstart + 2, rowstart, deList
								.getPaytwo().toString()));
						ws.addCell(new Label(colstart + 3, rowstart, deList
								.getPayall().toString()));
						ws.addCell(new Label(colstart + 4, rowstart, deList
								.getBxSum().toString()));
						rowstart++;
				} else {
					ws.addCell(new Label(colstart - 1, rowstart, type));
					ws.addCell(new Label(colstart , rowstart, deList
							.getPayone().toString()));
					 ws.mergeCells(colstart, rowstart, colstart+3, rowstart);
					 ws.addCell(new Label(colstart+4 , rowstart, deList
								.getBxSum().toString()));
					 rowstart++;
				}

			}
			ws.mergeCells(colstart - 2, begin, colstart - 2, rowstart - 1);
		 }
		return rowstart;
	}

	public void addData(List<Map> data, int colstart, int rowstart)
			throws RowsExceededException, WriteException {
		for (int k = 0; k < data.size(); k++) {
			Map<String, List> bm = (Map<String, List>) data.get(k);
			Iterator itype = bm.keySet().iterator();
			while (itype.hasNext()) {
				String type = itype.next().toString();
				BxMidle deList = (BxMidle) bm.get(type);
				if ("门诊".equals(type)) {
					ws.addCell(new Label(colstart, rowstart, deList.getPayone()
							.toString()));
					ws.addCell(new Label(colstart + 1, rowstart, deList
							.getPaytwo().toString()));
					ws.addCell(new Label(colstart + 2, rowstart, deList
							.getPayall().toString()));
					ws.addCell(new Label(colstart + 3, rowstart, deList
							.getBxSum().toString()));
				}
				if ("住院".equals(type)) {
					ws.addCell(new Label(colstart + 4, rowstart, deList
							.getPayone().toString()));
					ws.addCell(new Label(colstart + 5, rowstart, deList
							.getPaytwo().toString()));
					ws.addCell(new Label(colstart + 6, rowstart, deList
							.getPayall().toString()));
					ws.addCell(new Label(colstart + 7, rowstart, deList
							.getBxSum().toString()));
				}
				if ("包干费".equals(type)) {
					ws.addCell(new Label(colstart + 8, rowstart, deList
							.getPayall().toString()));
					ws.addCell(new Label(colstart + 9, rowstart, deList
							.getPayone().toString()));
					ws.addCell(new Label(colstart + 10, rowstart, deList
							.getBxSum().toString()));
				}
			}

		}
	}
	
	

}
